QuickSight で 1 つの Redshift に含まれる複数テーブルを集計する
コーヒーが好きな emi です。
QuickSight で 1 つの Redshift に含まれる複数テーブルを、以下 2 パターンで集計してみます。
- データセットのカスタム SQL クエリで集計するパターン
- 分析の計算フィールドやフィルターを駆使して集計するパターン
0. データの準備
以下の記事で Redshift のデータを可視化するアーキテクチャを構築しました。こちらの環境を利用していきます。
以下記事ではサンプルデータから users
テーブルのみを作成しましたが、追加で event
テーブルと sales
テーブルを作成してデータを格納していきます。
EC2 インスタンスにセッションマネージャーで接続し、Redshift にログインします。
psql -h <ホスト名> -U admin -d dev -p 5439
▼実行結果
sh-5.2$ psql -h 20241106-workgroup.123456789012.us-east-1.redshift-serverless.amazonaws.com -U admin -d dev -p 5439
Password for user admin:
psql (16.4, server 8.0.2)
WARNING: psql major version 16, server major version 8.0.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
dev=#
現在のスキーマを確認します。
select current_schema();
▼実行結果
dev=# select current_schema();
current_schema
----------------
public
(1 row)
dev=#
dev データベースではデフォルトで public というスキーマが使われています。今回はこのまま使用します。
Amazon S3 からデータをロードする を参考に、Redshift 側にテーブルを作っておきます。既に users
テーブルは準備できているので、event
テーブルと sales
テーブルを作成しましょう。
event
テーブル作成コマンド
create table event(
eventid integer not null distkey,
venueid smallint not null,
catid smallint not null,
dateid smallint not null sortkey,
eventname varchar(200),
starttime timestamp);
▼実行結果
dev=# create table event(
eventid integer not null distkey,
venueid smallint not null,
catid smallint not null,
dateid smallint not null sortkey,
eventname varchar(200),
starttime timestamp);
CREATE TABLE
dev=#
sales
テーブル作成コマンド
create table sales(
salesid integer not null,
listid integer not null distkey,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid smallint not null sortkey,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp);
▼実行結果
dev=# create table sales(
salesid integer not null,
listid integer not null distkey,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid smallint not null sortkey,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp);
CREATE TABLE
dev=#
event
テーブルが作成できたか、以下のコマンドで確認します。
select * from event;
▼実行結果
dev=# select * from event;
eventid | venueid | catid | dateid | eventname | starttime
---------+---------+-------+--------+-----------+-----------
(0 rows)
dev=#
sales
テーブルが作成できたか、以下のコマンドで確認します。
select * from sales;
▼実行結果
dev=# select * from sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission | saletime
---------+--------+----------+---------+---------+--------+---------+-----------+------------+----------
(0 rows)
dev=#
良いですね、テーブル作成できました。
続いて S3 バケットからデータコピーします。
4. サンプルデータの準備 の通り、あらかじめサンプルデータを S3 バケットに格納済みです。
COPY コマンドで、S3 バケットからサンプルデータを作成したテーブルにコピーします。
まずは event
テーブルにコピーします。
COPY event
FROM 's3://<S3 バケット名>/tickitdb/allevents_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516';
s3://<S3 バケット名>/tickitdb/allevents_pipe.txt
は、S3 に配置した「allevents_pipe.txt」の S3 URI です。IAM ロールは Redshift Serverless に付与されている「AmazonRedshift-CommandsAccessRole-xxxxxxxxxxxxxxx」を入力します。今回はバージニア北部リージョンで作業しているので、リージョンは us-east-1 です。数秒待つと以下のようにコピーが終了します。
▼実行結果
dev=# COPY event
FROM 's3://<S3 バケット名>/tickitdb/allevents_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516';
INFO: Load into table 'event' completed, 8797 record(s) loaded successfully.
COPY
dev=#
続いて sales
テーブルにコピーします。
COPY sales
FROM 's3://<S3 バケット名>/tickitdb/sales_tab.txt'
DELIMITER '\t'
TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516';
▼実行結果
dev=# COPY sales
FROM 's3://<S3 バケット名>/tickitdb/sales_tab.txt'
DELIMITER '\t'
TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::123456789012:role/service-role/AmazonRedshift-CommandsAccessRole-20241106T170516';
INFO: Load into table 'sales' completed, 172455 record(s) loaded successfully.
COPY
dev=#
以下のコマンドで、event
テーブルにデータがコピーできたか確認します。
select * from event;
実行結果
dev=# select * from event;
eventid | venueid | catid | dateid | eventname | starttime
---------+---------+-------+--------+------------------------------------------------------------------------+---------------------
1779 | 255 | 6 | 1838 | Folies Bergere | 2008-01-12 20:00:00
5487 | 27 | 9 | 1841 | George Jones | 2008-01-15 14:00:00
2345 | 230 | 7 | 1851 | To Be or Not To Be | 2008-01-25 19:30:00
1096 | 233 | 6 | 1859 | Grease | 2008-02-02 15:00:00
3767 | 225 | 7 | 1862 | The Farnsworth Invention | 2008-02-05 20:00:00
5598 | 79 | 9 | 1870 | Tower of Power | 2008-02-13 15:00:00
7596 | 32 | 9 | 1881 | Keith Urban | 2008-02-24 19:30:00
504 | 208 | 6 | 1883 | Legally Blonde | 2008-02-26 19:30:00
2751 | 224 | 7 | 1884 | A Bronx Tale | 2008-02-27 20:00:00
8303 | 101 | 9 | 1892 | Joe Satriani | 2008-03-07 15:00:00
61 | 303 | 8 | 1901 | L Elisir d Amore | 2008-03-16 14:00:00
2237 | 248 | 7 | 1903 | Miss Julie | 2008-03-18 14:00:00
3756 | 235 | 7 | 1904 | Waiting for Godot | 2008-03-19 20:00:00
8229 | 39 | 9 | 1914 | Anita Baker | 2008-03-29 19:30:00
4670 | 69 | 9 | 1919 | Foo Fighters | 2008-04-03 15:00:00
3858 | 126 | 9 | 1920 | Bo Bice | 2008-04-04 20:00:00
2040 | 231 | 7 | 1922 | The Bacchae | 2008-04-06 19:00:00
4672 | 13 | 9 | 1923 | The Police | 2008-04-07 19:00:00
8352 | 46 | 9 | 1933 | Motorhead | 2008-04-17 19:30:00
1471 | 239 | 6 | 1938 | Shrek the Musical | 2008-04-22 14:00:00
2946 | 230 | 7 | 1943 | All My Sons | 2008-04-27 19:00:00
2405 | 217 | 7 | 1950 | Wicked | 2008-05-01 20:00:00
522 | 221 | 6 | 1961 | Legally Blonde | 2008-05-01 14:00:00
2235 | 209 | 7 | 1973 | Uncle Vanya | 2008-05-01 19:00:00
4254 | 83 | 9 | 1992 | U2 | 2008-06-15 14:00:00
3997 | 88 | 9 | 1993 | Fleet Foxes | 2008-06-16 19:30:00
2016 | 210 | 7 | 1994 | The Homecoming | 2008-06-17 19:30:00
6874 | 71 | 9 | 1999 | Janet Jackson | 2008-06-22 15:00:00
5795 | 111 | 9 | 2002 | Irish Tenors | 2008-06-25 14:00:00
2523 | 243 | 7 | 2004 | The Cherry Orchard | 2008-06-27 14:30:00
148 | 302 | 8 | 2005 | Tristan und Isolde | 2008-06-28 20:00:00
590 | 240 | 6 | 2007 | Gypsy | 2008-06-30 19:30:00
3374 | 243 | 7 | 2007 | The Cherry Orchard | 2008-06-30 19:00:00
4900 | 71 | 9 | 2017 | Iron and Wine | 2008-07-10 19:30:00
3334 | 230 | 7 | 2018 | November | 2008-07-11 19:00:00
1542 | 255 | 6 | 2022 | Folies Bergere | 2008-07-15 19:00:00
8756 | 33 | 9 | 2027 | Black Kids | 2008-07-20 15:00:00
4549 | 79 | 9 | 2048 | Boston | 2008-08-10 19:30:00
4821 | 8 | 9 | 2049 | Dierks Bentley | 2008-08-11 15:00:00
1823 | 216 | 7 | 2067 | A Bronx Tale | 2008-08-29 20:00:00
946 | 241 | 6 | 2071 | State Fair | 2008-09-02 15:00:00
6954 | 111 | 9 | 2075 | Tegan and Sara | 2008-09-06 14:00:00
2492 | 224 | 7 | 2080 | Othello | 2008-09-11 20:00:00
7279 | 114 | 9 | 2084 | Godsmack | 2008-09-15 19:00:00
8218 | 90 | 9 | 2085 | Joan Baez | 2008-09-16 19:00:00
8442 | 12 | 9 | 2088 | Santana | 2008-09-19 15:00:00
8015 | 121 | 9 | 2091 | Ana Gabriel | 2008-09-22 15:00:00
7150 | 75 | 9 | 2100 | English Beat | 2008-10-01 19:30:00
3092 | 205 | 7 | 2109 | The Caretaker | 2008-10-10 14:00:00
434 | 307 | 8 | 2122 | Don Giovanni | 2008-10-23 14:00:00
1355 | 207 | 6 | 2124 | Kiss Me Kate | 2008-10-25 20:00:00
752 | 221 | 6 | 2125 | Memphis - The Birth of Rock n Roll | 2008-10-26 14:00:00
7783 | 62 | 9 | 2129 | George Thorogood and the Destroyers | 2008-10-30 14:00:00
4505 | 43 | 9 | 2134 | The Swell Season | 2008-11-04 14:30:00
4265 | 129 | 9 | 2142 | Echo & the Bunnymen | 2008-11-12 14:30:00
6955 | 61 | 9 | 2144 | Manhattan Transfer | 2008-11-14 19:00:00
877 | 225 | 6 | 2156 | Mamma Mia! | 2008-11-26 14:30:00
4886 | 2 | 9 | 2156 | Weird Al Yankovic | 2008-11-26 19:30:00
--More--Cancel request sent
7801 | 4 | 9 | 2156 | Kenny Loggins | 2008-11-26 14:00:00
dev=#
大量にデータが出力されるので、途中 Ctr + C で停止します。
以下のコマンドで、sales
テーブルにデータがコピーできたか確認します。
select * from sales;
実行結果
dev=# select * from sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission | saletime
---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------
70148 | 79760 | 23120 | 3341 | 7561 | 1832 | 1 | 339.00 | 50.85 | 2008-01-06 09:15:56
57080 | 64544 | 36196 | 4809 | 2160 | 1833 | 1 | 52.00 | 7.80 | 2008-01-07 01:05:02
103635 | 118530 | 30693 | 2038 | 7888 | 1834 | 2 | 358.00 | 53.70 | 2008-01-08 09:41:59
127565 | 146173 | 15094 | 2417 | 6665 | 1836 | 2 | 660.00 | 99.00 | 2008-01-10 08:52:51
104296 | 119322 | 29242 | 6085 | 6157 | 1838 | 1 | 207.00 | 31.05 | 2008-01-12 03:08:50
113969 | 130560 | 8486 | 3015 | 2071 | 1838 | 1 | 89.00 | 13.35 | 2008-01-12 04:05:47
54810 | 61756 | 8479 | 6887 | 8673 | 1839 | 2 | 954.00 | 143.10 | 2008-01-13 05:19:29
70149 | 79760 | 23120 | 9252 | 7561 | 1839 | 2 | 678.00 | 101.70 | 2008-01-13 09:15:58
89218 | 101889 | 11179 | 2658 | 6513 | 1840 | 4 | 1532.00 | 229.80 | 2008-01-14 01:34:26
92602 | 105702 | 28530 | 328 | 5457 | 1840 | 1 | 259.00 | 38.85 | 2008-01-14 05:34:38
159658 | 211204 | 8860 | 9935 | 838 | 1841 | 1 | 1007.00 | 151.05 | 2008-01-15 02:17:58
19010 | 20591 | 17361 | 4965 | 8072 | 1842 | 1 | 87.00 | 13.05 | 2008-01-16 06:47:47
120977 | 138471 | 7807 | 8481 | 2109 | 1842 | 4 | 928.00 | 139.20 | 2008-01-16 01:54:08
51760 | 58418 | 6093 | 563 | 650 | 1843 | 2 | 266.00 | 39.90 | 2008-01-17 07:00:40
52208 | 58916 | 8897 | 10772 | 2216 | 1843 | 1 | 499.00 | 74.85 | 2008-01-17 03:07:14
53488 | 60328 | 15434 | 395 | 218 | 1843 | 4 | 1504.00 | 225.60 | 2008-01-17 03:58:58
56730 | 64109 | 33032 | 2256 | 720 | 1843 | 1 | 207.00 | 31.05 | 2008-01-17 08:26:02
84174 | 96025 | 45693 | 406 | 8186 | 1843 | 1 | 137.00 | 20.55 | 2008-01-17 11:51:02
6108 | 6635 | 30023 | 14550 | 2426 | 1844 | 1 | 50.00 | 7.50 | 2008-01-18 06:46:19
6383 | 6934 | 12455 | 10973 | 5653 | 1844 | 2 | 278.00 | 41.70 | 2008-01-18 04:29:26
33555 | 37086 | 6504 | 5835 | 8250 | 1844 | 2 | 142.00 | 21.30 | 2008-01-18 06:17:16
92601 | 105702 | 28530 | 3610 | 5457 | 1844 | 4 | 1036.00 | 155.40 | 2008-01-18 05:33:36
33554 | 37086 | 6504 | 7480 | 8250 | 1845 | 4 | 284.00 | 42.60 | 2008-01-19 06:16:58
51759 | 58418 | 6093 | 3026 | 650 | 1846 | 1 | 133.00 | 19.95 | 2008-01-20 07:00:15
56094 | 63270 | 24354 | 11054 | 4802 | 1846 | 1 | 40.00 | 6.00 | 2008-01-20 08:14:13
68918 | 78356 | 9548 | 2349 | 4415 | 1846 | 1 | 459.00 | 68.85 | 2008-01-20 08:30:13
73714 | 84131 | 5923 | 10032 | 4689 | 1846 | 1 | 245.00 | 36.75 | 2008-01-20 11:28:30
94920 | 108362 | 14111 | 10245 | 3837 | 1846 | 2 | 324.00 | 48.60 | 2008-01-20 02:42:26
27750 | 30281 | 5892 | 2953 | 4830 | 1847 | 2 | 196.00 | 29.40 | 2008-01-21 04:02:50
39326 | 43884 | 7362 | 17163 | 4159 | 1847 | 2 | 676.00 | 101.40 | 2008-01-21 11:01:33
138782 | 160377 | 29057 | 8002 | 1010 | 1847 | 4 | 1920.00 | 288.00 | 2008-01-21 10:54:49
9253 | 10024 | 5382 | 3931 | 917 | 1849 | 2 | 166.00 | 24.90 | 2008-01-23 06:00:41
112240 | 128601 | 30401 | 9090 | 3227 | 1849 | 4 | 880.00 | 132.00 | 2008-01-23 02:02:30
9252 | 10024 | 5382 | 4480 | 917 | 1850 | 1 | 83.00 | 12.45 | 2008-01-24 05:58:12
123823 | 141764 | 17865 | 5231 | 5125 | 1850 | 1 | 94.00 | 14.10 | 2008-01-24 05:28:19
127180 | 145692 | 26758 | 18036 | 6588 | 1850 | 1 | 39.00 | 5.85 | 2008-01-24 12:02:38
153639 | 177870 | 33647 | 4916 | 4105 | 1850 | 1 | 275.00 | 41.25 | 2008-01-24 08:30:41
37054 | 41271 | 1727 | 10683 | 213 | 1852 | 2 | 546.00 | 81.90 | 2008-01-26 02:10:27
78496 | 89625 | 3401 | 199 | 1237 | 1852 | 1 | 351.00 | 52.65 | 2008-01-26 08:31:21
140908 | 162926 | 40154 | 8208 | 2984 | 1852 | 2 | 770.00 | 115.50 | 2008-01-26 03:58:54
142343 | 164692 | 27858 | 236 | 5068 | 1852 | 2 | 284.00 | 42.60 | 2008-01-26 07:27:58
43581 | 48995 | 20692 | 5028 | 2432 | 1853 | 4 | 132.00 | 19.80 | 2008-01-27 06:17:38
52209 | 58916 | 8897 | 18342 | 2216 | 1853 | 2 | 998.00 | 149.70 | 2008-01-27 03:07:48
117783 | 134879 | 6835 | 10858 | 4078 | 1853 | 1 | 488.00 | 73.20 | 2008-01-27 03:05:36
37483 | 41765 | 28464 | 7094 | 8490 | 1854 | 2 | 56.00 | 8.40 | 2008-01-28 09:57:57
89217 | 101889 | 11179 | 14450 | 6513 | 1854 | 2 | 766.00 | 114.90 | 2008-01-28 01:32:17
69416 | 78974 | 19846 | 21205 | 3767 | 1855 | 4 | 1940.00 | 291.00 | 2008-01-29 08:48:42
152608 | 176578 | 20871 | 7828 | 159 | 1855 | 2 | 314.00 | 47.10 | 2008-01-29 01:16:41
16315 | 17650 | 15868 | 3042 | 5688 | 1856 | 1 | 177.00 | 26.55 | 2008-01-30 02:07:36
113967 | 130560 | 8486 | 17904 | 2071 | 1856 | 2 | 178.00 | 26.70 | 2008-01-30 03:58:28
48367 | 54672 | 10673 | 8589 | 4761 | 1857 | 4 | 108.00 | 16.20 | 2008-01-31 02:23:17
18043 | 19550 | 35225 | 1359 | 4251 | 1858 | 2 | 224.00 | 33.60 | 2008-02-01 12:34:59
48365 | 54672 | 10673 | 9511 | 4761 | 1858 | 2 | 54.00 | 8.10 | 2008-02-01 02:22:11
120978 | 138471 | 7807 | 21804 | 2109 | 1858 | 2 | 464.00 | 69.60 | 2008-02-01 01:55:52
146508 | 169676 | 27560 | 7866 | 8463 | 1858 | 3 | 75.00 | 11.25 | 2008-02-01 02:25:44
109857 | 125765 | 16482 | 11440 | 5092 | 1859 | 1 | 171.00 | 25.65 | 2008-02-02 03:19:33
120205 | 137659 | 15018 | 3852 | 6815 | 1859 | 2 | 394.00 | 59.10 | 2008-02-02 02:41:36
33553 | 37086 | 6504 | 19888 | 8250 | 1860 | 1 | 71.00 | 10.65 | 2008-02-03 06:15:53
--More--Cancel request sent
16314 | 17650 | 15868 | 6710 | 5688 | 1861 | 3 | 531.00 | 79.65 | 2008-02-04 02:07:33
dev=#
1. データセットのカスタム SQL クエリで集計するパターン
QuickSight コンソールでデータセット画面を開き、「新しいデータセット」をクリックします。
データソースの作成で画面下部までスクロールすると、既存のデータソースから接続済みの Redshift が選択できるので選択します。スキーマで「public」を選択した状態で「カスタム SQL クエリを使用」をクリックします。
「データの編集/プレビュー」をクリックします。
カスタム SQL クエリの編集画面が開きます。
Amazon S3 からデータをロードする に記載されている集計クエリを参考に、データセットのカスタムクエリを入力します。
カスタム SQL クエリ名は「Find top 10 buyers by quantity.」としました。
SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;
「適用」をクリックすると、クエリ結果が画面下部に表示されます。
今回クエリモードは「直接クエリ(ダイレクトクエリ)」のまま、「保存して視覚化」で分析を作成します。
ビジュアルで「水平棒グラフ」を選択し、Y 軸に「firstname」、値に「total_quantity」を設定します。すると、以下キャプチャのように Jerry、Amando、Kameko…の順にグラフが表示されました。
「公開」をクリックしてダッシュボードを作成します。
ダッシュボードが作成できました。
2. 分析の計算フィールドやフィルターを駆使して集計するパターン
では、先ほどと同じグラフを、今度はカスタム SQL クエリを使わず、QuickSight 側の計算フィールドやフィルターを駆使して集計・再現します。
先ほどと同様にデータセットの作成画面で画面下部までスクロールし、既存のデータソースである Redshift を選択します。今度は「sales」テーブルをチェックし「選択」をクリックします。
「データクエリを直接実行(直接クエリ、ダイレクトクエリ)」を選択し、「データの編集/プレビュー」をクリックします。
「sales」テーブルの中身が画面下部にそのまま表示されています。ここで「データを追加」をクリックします。
データソースの選択画面で、先ほど同様 Redshift をチェックして「選択」をクリックします。
スキーマで「public」を選択し、users テーブルをチェックして「選択」をクリックします。
このように、視覚的に sales テーブルと users テーブルがブロックのように表示され、真ん中に赤い丸が二つ表示されています。
この二つの丸をクリックすると、画面下部に結合設定が現れます。この二つの丸はベン図のようなものなんですね。
今回は Sales から「buyerid」、users から「userid」を選択し、結合タイプは Inner join にします。userid をユニークキーにするので「右」にチェックします。
選択出来たら「適用」をクリックすると、
画面下部に、設定した通りにカラムが join(結合)されているのが分かります。「保存して視覚化」をクリックして分析を作成します。
先ほどと同様ビジュアルに水平棒グラフを選択し、Y 軸は firstname にしておきます。値に一旦 qtysold を入れましたが、先ほどのカスタム SQL クエリのように qtysold の合計が表示できていないので、計算フィールドを作成します。
名前を「total_quantity」とし、計算式を sum(qtysold)
として保存します。
また、名前を「rank_total_quantity」とし、計算式を rank([{total_quantity} DESC], [], POST_AGG_FILTER)
として保存します。この計算式は、集計されたtotal_quantity
の降順で並べられたデータに対して、全体で順位を計算し、フィルターや集計後のデータに基づいてランクを付けるものです。total_quantity
が大きい順に順位が割り当てられ、上位 10 位のレコードを可視化する目的で利用できます。
詳細
rank
rank
は指定された条件に基づいてデータに順位を付ける関数です。- 数値が大きい順や小さい順に並べたときにそれぞれのエントリの順位を割り当てます。
[total_quantity DESC]
- ランクを付けるためのソート条件を指定しています。
total_quantity
フィールドを降順(DESC
)でソートし、数値が大きいエントリから順に1位、2位、3位…と順位を計算します。total_quantity
が最も大きいエントリが 1 位になります。
[]
(空の配列)- 第二引数の
[]
は、partition by に相当する部分で、データをグループ化(パーティション分割)したい場合に使用します。 - 今回は空の配列
[]
になっているため、グループ化せずに全体で順位を計算しています。 - 特定の属性でグループごとにランクを付けたい場合は、ここにその属性を指定します。
- 第二引数の
POST_AGG_FILTER
POST_AGG_FILTER
は計算レベルを指定するもので、フィルターや集計後のデータに対してランクを計算するように指示しています。
計算フィールドを設定したら、ビジュアルで値の部分に「total_quantity」を入れます。グループ化に「buyerid」を入れます。
先ほど作成した「rank_total_quantity」でフィルターを設定します。
編集をクリックして、
- フィルター条件:次の間
- 最小値:1
- 最大値:10
と設定して適用します。線が細くてカラフルなので分かりにくいと思いますが、上から順番に Jerry、Amando、Kameko…の順にグラフが表示されており、「データセットのカスタム SQL クエリで集計するパターン」と同じグラフになりました。
「公開」をクリックして新しいダッシュボードとして保存します。
「データセットのカスタム SQL クエリで集計するパターン」のダッシュボードをもう一度貼ります。
同じグラフができたのが分かりますかね……?
おわりに
QuickSight で 1 つの Redshift に含まれる複数テーブルを以下二種類のパターンで集計してきました。
- データセットのカスタム SQL クエリで集計するパターン
- 分析の計算フィールドやフィルターを駆使して集計するパターン
カスタム SQL クエリで集計できる部分は集計してしまって、クエリでは集計しにくい場合に計算フィールドを使うのが良いです。
前日の値と今日の値の比較などは計算フィールドを使うと良いですね。
参考